package com.hbue.springboot.mapper;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;
import com.hbue.springboot.vo.WorkerSalaryVo;

@Repository
public interface WorkerSalaryMapper extends BaseMapper<WorkerSalaryVo> {

    @Select("SELECT SUM(h.send_water_count * w.worker_money) + w.worker_salary AS final_salary\n" +
            "FROM tb_worker w\n" +
            "INNER JOIN tb_history h ON w.id = h.worker_id\n" +
            "WHERE w.worker_name = #{workerName}\n" +
            "AND h.send_water_time >= #{beginTime}\n" +
            "AND h.send_water_time <= #{endTime}\n" +
            "AND w.deleted = 0\n" +
            "AND h.deleted = 0\n" +
            "group by w.id")
    Double getSalaryByName(@Param("workerName") String workerName,
                           @Param("beginTime") String beginTime, @Param("endTime") String endTime);

    @Select("select worker_salary from tb_worker where worker_name = #{workerName}")
    double getPayByName(String workerName);

    @Select("SELECT w.worker_name worker_name, w.worker_salary worker_salary, w.worker_money worker_money, \n" +
            "sum(h.send_water_count) send_water_count, \n" +
            "sum(h.send_water_count * w.worker_money) + w.worker_salary final_salary\n" +
            "FROM tb_worker w  inner join tb_history h  on w.id = h.worker_id\n" +
            "WHERE  w.deleted = 0 and h.deleted = 0\n" +
            "group by w.id\n" +
            "order by  final_salary desc")
    IPage<WorkerSalaryVo> listWorkerSalary(IPage<WorkerSalaryVo> page,
                                           @Param(Constants.WRAPPER) QueryWrapper<WorkerSalaryVo> queryWrapper);

    /**
     * 查询没有送水的送水工信息
     */
    @Select("(SELECT distinct w.id,w.worker_name, w.worker_salary, w.worker_money\n" +
            " from tb_worker w left join tb_history h on w.id = h.worker_id\n" +
            " ${ew.customSqlSegment}\n" +
            " and w.id not in (\n" +
            " select distinct worker_id from tb_history\n" +
            " ${ew2.customSqlSegment}) \n" +
            " and w.deleted = 0)\n" +
            " UNION \n" +
            " (select distinct w.id,w.worker_name, w.worker_salary, w.worker_money \n" +
            " from tb_worker w left join tb_history h on w.id = h.worker_id \n" +
            " where h.worker_id is null and w.deleted = 0 )")
    IPage<WorkerSalaryVo> listNonWorkerSalary
    (IPage<WorkerSalaryVo> page,
     @Param(Constants.WRAPPER) QueryWrapper<WorkerSalaryVo> queryWrapperOne,
     @Param("ew2") QueryWrapper<WorkerSalaryVo> queryWrapperTwo);
}
